import numpy
import pymysql

from connect_database.music_tools import MusicTool


class UserTool:
    def __init__(self):
        self.connect = pymysql.connect(host='localhost',  # 本地数据库
                                       port=3306,
                                       user='root',
                                       password='root',
                                       db='keyboard_shortcuts',
                                       charset='utf8',
                                       cursorclass=pymysql.cursors.DictCursor)  # 服务器名,账户,密码，数据库名称
        # 创建游标
        self.cur = self.connect.cursor()

    def __del__(self):
        print("用户数据库关闭")

    def close(self):
        self.cur.close()
        self.connect.close()

    # 创建用户
    def create_user(self, user_name: str, pass_word: str, role=0):
        # 插入用户
        sql = 'insert into user_info (user_name, pass_word, role) values (%s,%s,%s)'
        data = (user_name, pass_word, role)
        try:
            result = self.cur.execute(sql, data)
            if result:
                print("添加" + user_name + "成功")
                self.connect.commit()
                return True
            else:
                print("没有添加成功")
        except Exception as e:
            print(e)
        self.close()
        return False

    # 查询用户
    def get_user_info(self, user_name: str):
        # 查询一个用户
        sql = 'select * from user_info where user_name=(%s)'
        data = (user_name,)
        try:
            result = self.cur.execute(sql, data)
            if result:
                # 用户查询成功，返回数据
                result = self.cur.fetchone()
                user_id = result["user_id"]
                user_name = result["user_name"]
                pass_word = result["pass_word"]
                print("用户：", user_name)
                return user_id, user_name, pass_word
            else:
                print("用户查询不到")
        except Exception as e:
            print(e)
        self.close()
        return None, None, None

    # 增加用户收藏
    def insert_collections(self, user_id: int, music_id: int, is_collections=1):
        # 判断是否已经收藏
        sql = "select is_collections from user_collections where user_id={} and music_id={}".format(user_id, music_id)
        try:
            result = self.cur.execute(sql)
            # 返回为空或为0，表示没有该歌曲，可以添加
            if not result:
                sql = 'insert into user_collections(music_id, user_id, is_collections) values (%s, %s, %s)'
                data = (music_id, user_id, is_collections)
                try:
                    result = self.cur.execute(sql, data)
                    if result:
                        print("用户{0}收藏了{1}".format(user_id, music_id))
                        self.connect.commit()
                        return True
                except Exception as e:
                    print(e)
        except Exception as e:
            print(e)
        self.close()
        return False

    # 修改用户收藏
    def update_collections(self, user_id: int, music_id: int, is_collections: int):
        sql = "select is_collections from user_collections where user_id=%s and music_id=%s"
        # 查找用户是否收藏
        data = (user_id, music_id)
        try:
            result = self.cur.execute(sql, data)
            if result:
                # 该用户存在歌曲, 获取是否收藏
                result = self.cur.fetchone()
                is_collection = int(result[0]["is_collections"])
                if is_collection and is_collections:
                    # 已收藏
                    print("已收藏")
                    return False
                elif not (is_collection and is_collections):
                    print("没有收藏")
                    return False
                else:
                    # 用户已经收藏, 取消收藏。用户没有收藏, 想收藏
                    print("可以修改")
                    # 修改语句
                    sql = 'update user_collections set is_collections=%s where user_id=%s and music_id=%s'
                    data = (is_collections, user_id, music_id)
                    try:
                        self.cur.execute(sql, data)
                        self.connect.commit()
                        print("用户表修改成功")
                        return True
                    except Exception as e:
                        print(e)
            else:
                print("不存在用户")
        except Exception as e:
            print(e)
        print("用户已收藏")
        self.close()
        return False

    # 查询用户收藏歌曲信息
    def get_user_collections(self, user_name: str):
        user_id, _, _ = self.get_user_info(user_name)
        # 存放用户id收藏的音乐列表
        music_id_list = []
        # 存放用户id收藏的信息
        music_info_list = []
        # 取出所有的音乐id
        if user_id:
            # 存在用户
            sql = 'select music_id, is_collections from user_collections where user_id={}'.format(user_id)
            try:
                result = self.cur.execute(sql)
                if result:
                    # 用户收藏歌曲
                    music_id_dict = self.cur.fetchall()
                    for items in music_id_dict:
                        # 用户有收藏
                        if items["is_collections"]:
                            # 添加用户收藏得id
                            music_id_list.append(items["music_id"])
            except Exception as e:
                print(e)
        else:
            print("用户不存在")
            return None
        # 查找用户的音乐信息, 列表为空说明没有音乐存在
        if music_id_list:
            for music_id in music_id_list:
                # 音乐信息
                music_list = []
                sql = 'select music_name, audio_path, singer, music_duration from music_info where music_id={}'.format(
                    music_id)
                try:
                    self.cur.execute(sql)
                    # 获取数据
                    result = self.cur.fetchone()
                    for _, value in result.items():
                        music_list.append(value)
                except Exception as e:
                    print(e)
                # 将信息存放在总列表总
                music_info_list.append(music_list)
            return music_info_list
        else:
            print("用户{}没有收藏音乐".format(user_name))
        self.close()
        return None

    # 用户播放记录
    def get_user_record(self):
        user_list = []
        # 获取播放用户
        sql = "select user_id from music_record group by user_id"
        try:
            result = self.cur.execute(sql)
            if result:
                result = self.cur.fetchall()
                for items in result:
                    for _, value in items.items():
                        user_list.append(value)
            else:
                print("没有记录")
        except Exception as e:
            print(e)
        # 用户存在信息
        user_play_info = {}
        if user_list:
            try:
                for user in user_list:
                    sql = "select music_id, count(music_id) as play_num from music_record where user_id={} group by music_id".format(
                        user)
                    result = self.cur.execute(sql)
                    if result:
                        result = self.cur.fetchall()
                        user_all_music = []
                        for items in result:
                            user_music = (items["music_id"], items["play_num"])
                            user_all_music.append(user_music)
                        user_play_info[str(user)] = user_all_music
                    else:
                        print("用户{}没有音乐".format(user))
                return user_play_info
            except Exception as e:
                print(e)
        self.close()
        return None

    # 增加当前用户
    def insert_current_user(self, user_name: str):
        # 删除所有数据
        sql = 'delete from current_users'
        try:
            result = self.cur.execute(sql)
            if result:
                self.connect.commit()
                sql = "insert into current_users(user_name) value ({})".format(user_name)
                try:
                    result = self.cur.execute(sql)
                    if result:
                        self.connect.commit()
                        return True
                except Exception as e:
                    print(e)
        except Exception as e:
            print(e)
        self.close()
        return False

    # 获取当前用户
    def get_current_user(self):
        sql = "select * from current_users"
        try:
            result = self.cur.execute(sql)
            if result:
                result = self.cur.fetchone()
                return result["user_name"]
        except Exception as e:
            print(e)
        self.close()
        return None

    # 删除用户没有收藏的音乐
    def delete_user_no_colletions(self, user_id: int):
        sql = 'delete from user_collections where user_id={} and is_collections=0'.format(user_id)
        try:
            result = self.cur.execute(sql)
            if result:
                self.connect.commit()
                print("删除用户{0}{1}".format(user_id, result))
                return True
        except Exception as e:
            print(e)
        self.close()
        return False

    # 删除用户没有收藏的音乐
    def delete_user_colletions(self, user_id: int, music_id: int):
        sql = 'delete from user_collections where user_id={} and music_id={}'.format(user_id, music_id)
        try:
            result = self.cur.execute(sql)
            if result:
                self.connect.commit()
                print("删除用户{0}删除音乐{1},{2}条".format(user_id, music_id, result))
                return True
        except Exception as e:
            print(e)
        self.close()
        return False

    def get_all_id(self):
        sql = 'select user_id from user_info'
        user_all_id = []
        try:
            result = self.cur.execute(sql)
            if result:
                result = self.cur.fetchall()
                for item in result:
                    for _, value in item.items():
                        user_all_id.append(value)
                return user_all_id
        except Exception as e:
            print(e)
        self.close()
        return None

    # 修改用户密码
    def update_user_information(self, user_id: int, user_name: str, pass_word: str):
        user_name = user_name.strip(" ")
        pass_word = pass_word.strip(" ")
        sql = "update user_info set user_name='{}', pass_word='{}' where user_id={}".format(user_name, pass_word,
                                                                                            user_id)
        try:
            result = self.cur.execute(sql)
            if result:
                self.connect.commit()
                print("用户id{},修改修改信息user_name={}, pass_word={}".format(user_id, user_name, pass_word))
                return True
        except Exception as e:
            print(e)
        self.close()
        return False

    # 单个用户播放记录
    def get_one_user_record(self, user_id: int):
        sql = 'select music_id, count(music_id) from music_record where user_id={} group by music_id'.format(user_id)
        try:
            result = self.cur.execute(sql)
            if result:
                result = self.cur.fetchall()
                music_play_record = []
                for items in result:
                    mt = MusicTool()
                    music_name, audio_path, singer, music_duration = mt.get_one_music(items["music_id"])
                    music_play_record.append([music_name, audio_path, singer, music_duration])
                return music_play_record
        except Exception as e:
            print(e)
        self.close()
        return None

    # 删除用户播放记录
    def delete_user_play_record(self, user_id: int, music_name: str):
        sql = "select music_id from music_info where music_name='{}'".format(music_name)
        try:
            self.cur.execute(sql)
            result = self.cur.fetchone()
            for _, value in result.items():
                sql = "delete from music_record where music_id={} and user_id={}".format(value, user_id)
                self.cur.execute(sql)
                self.connect.commit()
                print("删除用户{}, 音乐为{}".format(user_id,value))
                return True
        except Exception as e:
            print(e)
        self.close()
        return False



if __name__ == '__main__':
    d = UserTool()
    music_info = d.delete_user_play_record(1, "Beautiful")
    print(music_info)
